﻿using BCCommon;
using BCEntity.Common.IP;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace BCData.Common.IP
{
    public class IPAddressRecordData : IIPAddressRecordData
    {
        protected readonly IDatabaseContext databaseContext;

        public IPAddressRecordData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public long Add(IPAddressRecordEntity ipAddressRecordEntity)
        {
            string sql = @"INSERT INTO IPAddressRecord(StartIPOne,StartIPTwo,StartIPThree,StartIPFour,EndIPOne,EndIPTwo,
                                    EndIPThree,EndIPFour,StartIP,EndIP,IANA,ReservedAddress)
                            VALUES(?StartIPOne,?StartIPTwo,?StartIPThree,?StartIPFour,?EndIPOne,?EndIPTwo,
                                    ?EndIPThree,?EndIPFour,?StartIP,?EndIP,?IANA,?ReservedAddress);
                            SELECT LAST_INSERT_ID();";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?StartIPOne", ipAddressRecordEntity.StartIPOne, DbType.Int32, size: 20);
            parameter.Add("?StartIPTwo", ipAddressRecordEntity.StartIPTwo, DbType.Int32, size: 20);
            parameter.Add("?StartIPThree", ipAddressRecordEntity.StartIPThree, DbType.Int32, size: 20);
            parameter.Add("?StartIPFour", ipAddressRecordEntity.StartIPFour, DbType.Int32, size: 20);
            parameter.Add("?EndIPOne", ipAddressRecordEntity.EndIPOne, DbType.Int32, size: 20);
            parameter.Add("?EndIPTwo", ipAddressRecordEntity.EndIPTwo, DbType.Int32, size: 20);
            parameter.Add("?EndIPThree", ipAddressRecordEntity.EndIPThree, DbType.Int32, size: 20);
            parameter.Add("?EndIPFour", ipAddressRecordEntity.EndIPFour, DbType.Int32, size: 20);
            parameter.Add("?StartIP", ipAddressRecordEntity.StartIP, DbType.AnsiString, size: 20);
            parameter.Add("?EndIP", ipAddressRecordEntity.EndIP, DbType.AnsiString, size: 20);
            parameter.Add("?IANA", ipAddressRecordEntity.IANA, DbType.AnsiString, size: 50);
            parameter.Add("?ReservedAddress", ipAddressRecordEntity.ReservedAddress, DbType.AnsiString, size: 100);
            return this.databaseContext.Master.ExecuteScalar<long>(sql, parameter, databaseContext.Transaction);
        }

        public bool CheckIPAddressRecordExists(IPAddressRecordEntity ipAddressRecordEntity)
        {
            string sql = @"SELECT 1 FROM IPAddressRecord WHERE StartIP=?StartIP AND EndIP=?EndIP LIMIT 1;";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?StartIP", ipAddressRecordEntity.StartIP, DbType.AnsiString, size: 20);
            parameter.Add("?EndIP", ipAddressRecordEntity.EndIP, DbType.AnsiString, size: 20);
            object result = this.databaseContext.Slave.ExecuteScalar(sql, parameter);
            return result != null;
        }

        public IPAddressRecordEntity Get(IPAddressRecordEntity ipAddressRecordEntity)
        {
            string sql = @"SELECT StartIPOne,StartIPTwo,StartIPThree,StartIPFour,EndIPOne,EndIPTwo,
                                    EndIPThree,EndIPFour,StartIP,EndIP,IANA,ReservedAddress
                            FROM ipaddressrecord 
                            WHERE StartIPOne=?IPOne AND EndIPOne=?IPOne 
                            AND StartIPTwo=?IPTwo AND EndIPTwo=?IPTwo 
                            AND ?IPThree BETWEEN StartIPThree AND EndIPThree
                            AND ?IPFour BETWEEN StartIPFour AND EndIPFour 
                            LIMIT 1;";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?IPOne", ipAddressRecordEntity.StartIPOne, DbType.Int32, size: 20);
            parameter.Add("?IPTwo", ipAddressRecordEntity.StartIPTwo, DbType.Int32, size: 20);
            parameter.Add("?IPThree", ipAddressRecordEntity.StartIPThree, DbType.Int32, size: 20);
            parameter.Add("?IPFour", ipAddressRecordEntity.StartIPFour, DbType.Int32, size: 20);
            return this.databaseContext.Slave.QueryFirstOrDefault<IPAddressRecordEntity>(sql, parameter);
        }

        public bool Update(IPAddressRecordEntity ipAddressRecordEntity)
        {
            string sql = @"UPDATE IPAddressRecord SET
                                IANA=?IANA,
                                ReservedAddress=?ReservedAddress
                            WHERE StartIP=?StartIP AND EndIP=?EndIP;";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?StartIP", ipAddressRecordEntity.StartIP, DbType.AnsiString, size: 20);
            parameter.Add("?EndIP", ipAddressRecordEntity.EndIP, DbType.AnsiString, size: 20);
            parameter.Add("?IANA", ipAddressRecordEntity.IANA, DbType.AnsiString, size: 50);
            parameter.Add("?ReservedAddress", ipAddressRecordEntity.ReservedAddress, DbType.AnsiString, size: 100);
            int result = this.databaseContext.Master.Execute(sql, parameter, databaseContext.Transaction);
            return result > 0;
        }
    }
}
